/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.upc.movilidad.dao;

import edu.upc.movilidad.beans.DdjjMovilidad;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author SEGOVIA
 */
public class MovilidadDao {
    
    public List<DdjjMovilidad> findAll() {
        List<DdjjMovilidad> list = new ArrayList<DdjjMovilidad>();
        Connection c = null;
    	String sql = "SELECT * FROM ddjj_movilidad ORDER BY codigo_ddjj";
        try {
            c = ConnectionHelper.getConnection();
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                list.add(processRow(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
        return list;
    }

    
    public List<DdjjMovilidad> findByName(String name) {
        List<DdjjMovilidad> list = new ArrayList<DdjjMovilidad>();
        Connection c = null;
    	String sql = "SELECT * FROM ddjj_movilidad as e " +
			"WHERE UPPER(observaciones) LIKE ? " +	
			"ORDER BY observaciones";
        try {
            c = ConnectionHelper.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, "%" + name.toUpperCase() + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(processRow(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
        return list;
    }
    
    public DdjjMovilidad findById(int id) {
    	String sql = "SELECT * FROM ddjj_movilidad WHERE codigo_ddjj = ?";
        DdjjMovilidad wine = null;
        Connection c = null;
        try {
            c = ConnectionHelper.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                wine = processRow(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
        return wine;
    }

    public DdjjMovilidad save(DdjjMovilidad wine)
	{
		return wine.getCodigoDdjj()>0 ? update(wine) : create(wine);
	}    
    
    public DdjjMovilidad create(DdjjMovilidad wine) {
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = ConnectionHelper.getConnection();
            ps = c.prepareStatement("INSERT INTO ddjj_movilidad (codigo_ddjj, observaciones) VALUES (?, ?)",
                new String[] { "ID" });
            ps.setString(1, wine.getCodigoEmplPer());
            ps.setInt(2, wine.getCodigoResumen());
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            rs.next();
            // Update the id in the returned object. This is important as this value must be returned to the client.
             int id = rs.getInt(1);
            wine.setCodigoDdjj(id);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
        return wine;
    }

    public DdjjMovilidad update(DdjjMovilidad wine) {
        Connection c = null;
        try {
            c = ConnectionHelper.getConnection();
            PreparedStatement ps = c.prepareStatement("UPDATE ddjj_movilidad SET observaciones=? WHERE codigo_ddjj=?");
            ps.setString(1, wine.getCodigoEmplPer());
            ps.setInt(2, wine.getCodigoResumen());
            ps.setInt(3, wine.getCodigoDdjj());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
        return wine;
    }

    public boolean remove(int id) {
        Connection c = null;
        try {
            c = ConnectionHelper.getConnection();
            PreparedStatement ps = c.prepareStatement("DELETE FROM ddjj_movilidad WHERE codigo_ddjj=?");
            ps.setInt(1, id);
            int count = ps.executeUpdate();
            return count == 1;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
		} finally {
			ConnectionHelper.close(c);
		}
    }

    protected DdjjMovilidad processRow(ResultSet rs) throws SQLException {
        DdjjMovilidad wine = new DdjjMovilidad();
        wine.setCodigoDdjj(rs.getInt("codigo_ddjj"));
        wine.setCodigoEmplPer(rs.getString("codigo_empl_per"));
        wine.setCodigoResumen(rs.getInt("codigo_resumen"));
        return wine;
    }
    
    
}
